from datetime import datetime
from typing import Union, Optional

from sqlalchemy import update, CursorResult, text, func, insert, delete
from sqlalchemy.ext.asyncio import AsyncSession
from sqlmodel import select, desc, col, asc, and_
from sqlmodel.sql._expression_select_cls import SelectOfScalar, Select

from src.base.beans import PageResult
from src.base.constants import id_generator
from src.config.decorator import data_scope, tenant
from src.domain.sys_menu import SysMenu, SysMenuReq
from src.domain.sys_role import SysRole
from src.domain.sys_role_meun import SysRoleMenu
from src.domain.sys_user import SysUser
from src.domain.sys_user_role import SysUserRole


async def get_menu_by_id(dbSession: AsyncSession, id: int) -> SysMenu:
    stmt = (
        select(SysMenu)
        .select_from(SysMenu)
        .outerjoin(SysUser, col(SysUser.user_id).__eq__(SysMenu.creator), )
        .where(SysMenu.menu_id == id).where(SysMenu.del_flag == False)
    )
    result = await dbSession.execute(stmt)
    return result.scalar_one_or_none()


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysMenuReq = None):
    stmt = stmt.where(SysMenu.del_flag == False)
    if entity:
        if entity.menu_id:
            stmt = stmt.where(SysMenu.menu_id == entity.menu_id)
        if entity.menu_name:
            stmt = stmt.where(col(SysMenu.menu_name).like(f"%{entity.menu_name}%"))
        if entity.menu_code:
            stmt = stmt.where(SysMenu.menu_code == entity.menu_code)
        if entity.status is not None:
            stmt = stmt.where(SysMenu.status == entity.status)
        if entity.create_time_range:
            start_time = datetime.strptime(entity.create_time_range[0], "%Y-%m-%d").date()
            end_time = datetime.strptime(entity.create_time_range[1], "%Y-%m-%d").date()
            stmt = stmt.where(
                and_(func.date(SysMenu.create_time) >= start_time, func.date(SysMenu.create_time) <= end_time))
    return stmt


async def get_menu_list_by_condition(dbSession: AsyncSession, entity: Optional[SysMenuReq] = None) -> list[SysMenu]:
    if entity is None:
        entity = SysMenu()
    stmt = select(SysMenu)
    stmt = where_handle(stmt, entity)
    stmt = stmt.order_by(desc(SysMenu.sort))
    result = await dbSession.execute(stmt)
    return list(result.scalars())


async def get_menu_page_by_condition(dbSession: AsyncSession, entity: Optional[SysMenuReq] = None) -> PageResult:
    if entity is None:
        entity = SysMenuReq()
    search = SysMenu(**entity.model_dump())
    list_stmt = select(SysMenu)
    list_stmt = where_handle(list_stmt, entity)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysMenu.sort))
    list_stmt = list_stmt.limit(entity.pageSize).offset(entity.pageSize * (entity.current - 1))
    data = await dbSession.execute(list_stmt)
    data = list(data.scalars())

    total_stmt = select(func.count(SysMenu.menu_id))
    total_stmt = where_handle(total_stmt, entity)
    total_result = await dbSession.execute(total_stmt)
    total = total_result.scalar_one_or_none()
    return PageResult(total=total, data=data, current=entity.current, pageSize=entity.pageSize)


async def insert_menu(dbSession: AsyncSession, entity: SysMenuReq):
    entity.menu_id = next(id_generator)
    insert_data = SysMenu(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    result = await dbSession.execute(insert(SysMenu).values(**insert_data))
    entity.menu_id = result.lastrowid
    return int(result.rowcount)


async def update_menu(dbSession: AsyncSession, entity: SysMenuReq) -> int:
    update_data = SysMenu(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    stmt = update(SysMenu).values(**update_data).where(col(SysMenu.menu_id).__eq__(entity.menu_id))
    result: CursorResult = await dbSession.execute(stmt)
    return int(result.rowcount)


async def delete_menu(dbSession: AsyncSession, id: int = None) -> int:
    # stmt = update(SysMenu).values({"del_flag": True}).where(col(SysMenu.menu_id).__eq__(id))
    stmt = delete(SysMenu).where(col(SysMenu.menu_id).__eq__(id))
    result: CursorResult = await dbSession.execute(stmt)
    return int(result.rowcount)


async def delete_menus(dbSession: AsyncSession, ids: list[int] = None) -> int:
    # stmt = update(SysMenu).values({"del_flag": True}).where(col(SysMenu.menu_id).in_(ids))
    stmt = delete(SysMenu).where(col(SysMenu.menu_id).in_(ids))
    result: CursorResult = await dbSession.execute(stmt)
    return int(result.rowcount)


# def get_menu_list_by_role_ids(role_ids: list[int]):
#     menu_sql = text(
#         "select distinct m.* from sys_menu m left join sys_role_menu rm on m.menu_id = rm.menu_id where rm.role_id in :role_ids")
#     menu_stmt = select(SysMenu).from_statement(menu_sql)
#     return mysqlClient.exec(menu_stmt, params={"role_ids": tuple(role_ids)}).scalars().all()

async def get_menu_list_by_role_ids(dbSession: AsyncSession, role_ids: list[int]):
    stmt = (select(SysMenu)
            .select_from(SysMenu)
            .outerjoin(SysRoleMenu, col(SysRoleMenu.menu_id).__eq__(SysMenu.menu_id), )
            .where(col(SysRoleMenu.menu_id).in_(role_ids)))
    result = await dbSession.execute(stmt)
    return list(result.scalars())


@tenant("sys_menu", )
@data_scope("sys_user", "sys_menu", user_id_alias="creator")
async def get_menu_list_by_user_id(dbSession: AsyncSession, user_id: int):
    stmt = (
        select(SysMenu)
        .select_from(SysMenu)
        .outerjoin(SysRoleMenu, col(SysMenu.menu_id).__eq__(SysRoleMenu.menu_id), )
        .outerjoin(SysRole, col(SysRole.role_id).__eq__(SysRoleMenu.role_id), )
        .outerjoin(SysUserRole, col(SysRole.role_id).__eq__(SysUserRole.role_id))
        .outerjoin(SysUser, col(SysUser.user_id).__eq__(SysUserRole.user_id))
        .where(col(SysUserRole.user_id).__eq__(user_id))
    )
    stmt = stmt.order_by(desc(SysMenu.sort))
    result = await dbSession.execute(stmt)
    return list(result.scalars())
